import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

public class test {
    public static void main(String[] args) {
        // 数据库连接字符串
        String url = "jdbc:mysql://localhost:3306/test";
        // 数据库用户名
        String user = "root";
        // 数据库密码
        String password = "123456";

        try {
            // 加载新的驱动
            Class.forName("com.mysql.cj.jdbc.Driver");

            // 获取连接
            Connection connection = DriverManager.getConnection(url, user, password);

            // 插入数据
            PreparedStatement statement = connection.prepareStatement("INSERT INTO student (name, pwd, sex, birthday, address, email) VALUES (?,?,?,?,?,?)");
            statement.setString(1, "Alice");
            statement.setString(2, "password123");
            statement.setString(3, "Female");
            statement.setString(4, "1990-01-01");
            statement.setString(5, "Address Line 1");
            statement.setString(6, "alice@example.com");
            statement.executeUpdate();

            // 查询数据
            statement = connection.prepareStatement("SELECT * FROM student");
            ResultSet resultSet = statement.executeQuery();
            while (resultSet.next()) {
                int id = resultSet.getInt("id");
                String name = resultSet.getString("name");
                String pwd = resultSet.getString("pwd");
                String sex = resultSet.getString("sex");
                String birthday = resultSet.getString("birthday");
                String address = resultSet.getString("address");
                String email = resultSet.getString("email");
                System.out.println("ID: " + id);
                System.out.println("Name: " + name);
                System.out.println("Pwd: " + pwd);
                System.out.println("Sex: " + sex);
                System.out.println("Birthday: " + birthday);
                System.out.println("Address: " + address);
                System.out.println("Email: " + email);
            }

            // 更新数据
            statement = connection.prepareStatement("UPDATE student SET name =?, address =? WHERE id =?");
            statement.setString(1, "Bob");
            statement.setString(2, "Address Line 2");
            statement.setInt(3, 1);  // 替换为要更新的 ID
            statement.executeUpdate();

            // 删除数据
            statement = connection.prepareStatement("DELETE FROM student WHERE id =?");
            statement.setInt(1, 1);  // 替换为要删除的 ID
            statement.executeUpdate();

            // 关闭资源
            resultSet.close();
            statement.close();
            connection.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}